﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public partial class WebSite_m_data_up : System.Web.UI.Page
{
    string SQLRE;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["UserId"] == null || Session["UserIdentity"].ToString() != "2")
        {
            Response.Redirect("Default.aspx", false);
            HttpContext.Current.ApplicationInstance.CompleteRequest();
            return;
        }

        return;
    }
    //////////////////////////////////////////////////////////////////////////
    /// <summary>
    /// 点击button上传文件时的事件
    /// </summary>
    //////////////////////////////////////////////////////////////////////////
    protected void btnFileUpload_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
            if (System.IO.Path.GetExtension(FileUpload1.FileName) != ".xls")
            {
                btnFileUpload.Text = "只能上传.xls文件！，请重传";
                return;
            }
            else
            {

                try
                {
                    //上传并指定上传的目录
                    string FilePath = Server.MapPath("~/UploadFile/UploadData/") + FileUpload1.FileName;
                    FileUpload1.PostedFile.SaveAs(FilePath);
                    btnFileUpload.Text = "上传成功，正在做处理";
                    //将xls文件中的数据读入到数据库中
                    ReadxlsIntoDataBase(FilePath);
                }
                catch (System.Exception ex)
                {
                    btnFileUpload.Text = "上传异常，请重新做上传！";
                }
            }
          
        }
    }
    //////////////////////////////////////////////////////////////////////////
    /// <summary>
    /// 将高校数据导入模板的数据从excel里面读出来，并写入到数据库中
    /// 1)如果是新用户，则自动添加该用户的注册信息，用户名和密码均为学号
    /// 2)如果是老用户，则直接开始添加该用户的体测信息
    /// 3)如果该用户今年的体测信息在表中已经存在，说明该用户之前已经做过体测，所以更新他的成绩
    /// 4)如果用户今年的体测信息没有在表中存在，则直接添加该用户今年的成绩。
    /// 由于无法从excel中获得用户的院系信息，所以这一项还无法录入
    /// </summary>
    /// <param name="FilePath">Excel文件的绝对路径名</param>
    //////////////////////////////////////////////////////////////////////////
    private void ReadxlsIntoDataBase(string FilePath)
    {       
#region 将excel中内容读入到DataSet中去

        string OledbConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + FilePath + "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'");
        OleDbConnection conn = new OleDbConnection(OledbConnectionString);
        ArrayList SheetNameList = new ArrayList();
        //获取excel表中表格的数量和分别名称，存到SheetNameList里面
        try
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            DataTable dtExcelSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            string SheetName = "";
            for (int i = 0; i < dtExcelSchema.Rows.Count; i++)
            {
                SheetName = dtExcelSchema.Rows[i]["TABLE_NAME"].ToString();
                SheetNameList.Add(SheetName);
            }

        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            conn.Close();
        }
        //将excel中表格所有数据分别填入到DataSet dsExcel里面
        DataSet dsExcel = new DataSet();
        string strSql = "";
        try
        {

            for (int i = 0; i < SheetNameList.Count; i++)
            {
                strSql = "select * from [" + (string)SheetNameList[i] + "]";
                OleDbDataAdapter oleExcelDataAdapter = new OleDbDataAdapter(strSql, conn);
                DataTable dtExcel = new DataTable((string)SheetNameList[i]);
                oleExcelDataAdapter.Fill(dtExcel);
                dsExcel.Tables.Add(dtExcel);
            }
        }
        catch (Exception ex)
        {
            //跳转到错误处理页面。
            Session["ErrorMessage"] = ex.Message + "/nSQL:" + strSql ;
            Session["AtPage"] = "m_data_up.aspx";
            Response.Redirect("m_error.aspx", false);
            HttpContext.Current.ApplicationInstance.CompleteRequest();
            return;
        }
#endregion
#region 将DataSet里面的数据添加到数据库中
        SqlConnection Sqlconn = new SqlConnection("Data Source=Localhost;Integrated Security=SSPI;Initial Catalog=CSHDB");
        Sqlconn.Open();
        int num = 0;
        string sex;
        string realname, gradenum,stdnum,collegename, nationalityname, nationalitynum, citynum, staminanum,strengthnum,speednum,othernum;
        string Borndate;
        string height,weight,vitalcapacity,staminacapacity,strengthcapacity,speedcapacity;
        string password,HeightWeightLevel,VitalLevel,StaminaLevel,StrengthLevel,SpeedLevel,AllLevel;
        string HeightWeightMark, VitalMark, StaminaMark, StrengthMark, SpeedMark, AllMark;

        try
        {
            int i = 0,j=0;
           
                for (j = 0; j < dsExcel.Tables[i].Rows.Count; j++)
                {
                    //获取行信息
                    stdnum = dsExcel.Tables[i].Rows[j]["学号"].ToString();
                    //如果该行为空行，则说明该文件中所有行都录入完毕，退出录入数据库
                    if (stdnum.Length==0)
                    {
                        break;
                    }
                    gradenum = dsExcel.Tables[i].Rows[j]["年级编号"].ToString();         
                    nationalitynum = dsExcel.Tables[i].Rows[j]["民族代码"].ToString();
                    realname= dsExcel.Tables[i].Rows[j]["姓名"].ToString();
                    collegename = dsExcel.Tables[i].Rows[j]["班级"].ToString();
                    sex = dsExcel.Tables[i].Rows[j]["性别"].ToString();
                    if (sex.Trim()=="1")
                    {
                        sex = "0";
                    }
                    else if (sex.Trim()=="2")
                    {
                        sex = "1";
                    }
                    //数据库中0为男，1为女，在excel中是1为男，2为女。所以减一
                    Borndate =dsExcel.Tables[i].Rows[j]["出生日期"].ToString();
                    citynum = dsExcel.Tables[i].Rows[j]["学生来源"].ToString();
                    height = dsExcel.Tables[i].Rows[j]["身高"].ToString();
                    weight = dsExcel.Tables[i].Rows[j]["体重"].ToString();
                    vitalcapacity = dsExcel.Tables[i].Rows[j]["肺活量"].ToString();
                    staminanum = dsExcel.Tables[i].Rows[j]["耐力类项目编号"].ToString();
                    staminacapacity = dsExcel.Tables[i].Rows[j]["耐力类项目成绩"].ToString();
                    strengthnum = dsExcel.Tables[i].Rows[j]["柔韧、力量类项目编号"].ToString();
                    strengthcapacity = dsExcel.Tables[i].Rows[j]["柔韧、力量类项目成绩"].ToString();
                    speednum = dsExcel.Tables[i].Rows[j]["速度、灵巧类项目编号"].ToString();
                    speedcapacity = dsExcel.Tables[i].Rows[j]["速度、灵巧类项目成绩"].ToString();
                    othernum = dsExcel.Tables[i].Rows[j]["奖惩项目编号"].ToString();
                    password = dsExcel.Tables[i].Rows[j]["家庭住址"].ToString();
                    if (password.Length - 6 >= 0)
                        password = password.Substring(password.Length - 6, 6);
                    else
                        password = password.PadRight(6,'x');
                    HeightWeightLevel = dsExcel.Tables[i].Rows[j]["身高体重等级"].ToString();
                    HeightWeightMark = dsExcel.Tables[i].Rows[j]["身高体重分数"].ToString();
                    VitalLevel = dsExcel.Tables[i].Rows[j]["肺活量体重等级"].ToString();
                    VitalMark = dsExcel.Tables[i].Rows[j]["肺活量体重分数"].ToString();
                    StaminaLevel = dsExcel.Tables[i].Rows[j]["耐力类项目等级"].ToString();
                    StaminaMark = dsExcel.Tables[i].Rows[j]["耐力类项目分数"].ToString();
                    StrengthLevel = dsExcel.Tables[i].Rows[j]["柔韧、力量类项目等级"].ToString();
                    StrengthMark = dsExcel.Tables[i].Rows[j]["柔韧、力量类项目分数"].ToString();
                    SpeedLevel = dsExcel.Tables[i].Rows[j]["速度、灵巧类项目等级"].ToString();
                    SpeedMark = dsExcel.Tables[i].Rows[j]["速度、灵巧类项目分数"].ToString();
                    AllLevel = dsExcel.Tables[i].Rows[j]["总分等级"].ToString();
                    AllMark = dsExcel.Tables[i].Rows[j]["测试总分"].ToString();

                    //填充注册信息
                    string sqlstr = "select * from UserInfo where UserId=" + stdnum;
                    SqlCommand sqlcomm = new SqlCommand(sqlstr, Sqlconn);
                    SqlDataReader sqlrr = sqlcomm.ExecuteReader();
                    //如果以前没有注册过，则填充该用户的注册信息，用户名密码为身份证号后六位
                    if (!sqlrr.HasRows)
                    {
                        //向用户注册信息表中添加用户注册信息
                        sqlrr.Dispose();
                        sqlstr = "insert into UserInfo(UserId,PassWord,UserIdentity,CountNO) values('" + stdnum + "','" + password + "',0,0)";
                        sqlcomm = new SqlCommand(sqlstr, Sqlconn);
                        sqlcomm.ExecuteNonQuery();

                        //从民族代码获得民族名称
                        sqlstr = "select Nationality from NationalityTable where NationalityId="+nationalitynum;
                        sqlcomm=new SqlCommand(sqlstr,Sqlconn);
                        sqlrr=sqlcomm.ExecuteReader();
                        sqlrr.Read();
                        nationalityname=sqlrr.GetValue(0).ToString();

                        //从行政代码获得所在生源地名称
                        sqlrr.Dispose();
                        sqlstr = "select CityName from ProvinceTable where CityId="+citynum;
                        sqlcomm=new SqlCommand(sqlstr,Sqlconn);
                        sqlrr=sqlcomm.ExecuteReader();
                        sqlrr.Read();
                        string cityname=sqlrr.GetValue(0).ToString();


                        //向用户详细信息表中添加用户详细信息
                        sqlrr.Dispose();
                        sqlstr = "insert into UserDetail(UserId,Name,BornDate,Location,Nationality,Sex,Grade,CollegeName) values('" + stdnum + "','"
                            + realname + "','" + Borndate + "','" + cityname + "','" + nationalityname + "',"+sex+"," + gradenum+",'"+collegename+"')";
                        sqlcomm = new SqlCommand(sqlstr, Sqlconn);
                        sqlcomm.ExecuteNonQuery();
                    }
                    //向表PhtsiqueTest中填入该生的体测信息,这里的TestDate其实是将体测信息加入表的日期
                    string targetname;
                    DateTime date = new DateTime(DateTime.Today.Year,1,1,0,0,0);
                    
                    //查看表中是否记录了该生今年的记录
                    sqlrr.Dispose();
                    sqlstr = "select * from PhysiqueTest where UserId='" + stdnum +"' AND "+"TestDate='"+date.ToString("d")+"'";
                    sqlcomm = new SqlCommand(sqlstr, Sqlconn);
                    sqlrr = sqlcomm.ExecuteReader();
                    sqlrr.Read();
                    //如果表中还没有该生今年的体测信息，则添加体测信息,使用Insert语句
                    if (!sqlrr.HasRows)
                    {
                        //添加身高信息
                        sqlrr.Dispose();
                        sqlstr = "insert into PhysiqueTest(UserId,TestName,Result,TestDate) values('" + stdnum + "'," + "'身高'," + height + ",'" + date.ToString("d") + "')";
                        sqlcomm = new SqlCommand(sqlstr, Sqlconn);
                        sqlcomm.ExecuteNonQuery();

                        //添加体重信息
                        sqlstr = "insert into PhysiqueTest(UserId,TestName,Result,TestDate) values('" + stdnum + "'," + "'体重'," + weight + ",'" + date.ToString("d") + "')";
                        sqlcomm = new SqlCommand(sqlstr, Sqlconn);
                        sqlcomm.ExecuteNonQuery();

                        //添加肺活量
                        sqlstr = "insert into PhysiqueTest(UserId,TestName,Result,TestDate) values('" + stdnum + "'," + "'肺活量'," + vitalcapacity + ",'" + date.ToString("d") + "')";
                        sqlcomm = new SqlCommand(sqlstr, Sqlconn);
                        sqlcomm.ExecuteNonQuery();

                        //添加耐力项目
                        sqlrr.Dispose();
                        sqlstr = "select TestName from TaskIDTable where TestId=" + staminanum;
                        sqlcomm = new SqlCommand(sqlstr, Sqlconn);
                        sqlrr = sqlcomm.ExecuteReader();
                        sqlrr.Read();
                        targetname = sqlrr.GetValue(0).ToString();

                        sqlrr.Dispose();
                        sqlstr = "insert into PhysiqueTest(UserId,TestName,Result,TestDate) values('" + stdnum + "','" + targetname + "'," + staminacapacity+ ",'" + date.ToString("d") + "')";
                        sqlcomm = new SqlCommand(sqlstr, Sqlconn);
                        sqlcomm.ExecuteNonQuery();

                        //添加柔韧力量项目
                        sqlrr.Dispose();
                        sqlstr = "select TestName from TaskIDTable where TestId=" + strengthnum;
                        sqlcomm = new SqlCommand(sqlstr, Sqlconn);
                        sqlrr = sqlcomm.ExecuteReader();
                        sqlrr.Read();
                        targetname = sqlrr.GetValue(0).ToString();

                        sqlrr.Dispose();
                        sqlstr = "insert into PhysiqueTest(UserId,TestName,Result,TestDate) values('" + stdnum + "','" + targetname + "'," + strengthcapacity + ",'" + date.ToString("d") + "')";
                        sqlcomm = new SqlCommand(sqlstr, Sqlconn);
                        sqlcomm.ExecuteNonQuery();


                        //添加速度灵巧类项目
                        sqlrr.Dispose();
                        sqlstr = "select TestName from TaskIDTable where TestId=" + speednum;
                        sqlcomm = new SqlCommand(sqlstr, Sqlconn);
                        sqlrr = sqlcomm.ExecuteReader();
                        sqlrr.Read();
                        targetname = sqlrr.GetValue(0).ToString();

                        sqlrr.Dispose();
                        sqlstr = "insert into PhysiqueTest(UserId,TestName,Result,TestDate) values('" + stdnum + "','" + targetname + "'," + speedcapacity + ",'" + date.ToString("d") + "')";
                        sqlcomm = new SqlCommand(sqlstr, Sqlconn);
                        sqlcomm.ExecuteNonQuery();  

                        //添加奖惩项目信息
                        if (othernum.Length == 0)
                        {
                            targetname = "NONE";

                        }
                        else
                        {
                            sqlrr.Dispose();
                            sqlstr = "select TestName from TaskIDTable where TestId=" + othernum;
                            sqlcomm = new SqlCommand(sqlstr, Sqlconn);
                            sqlrr = sqlcomm.ExecuteReader();
                            sqlrr.Read();
                            targetname = sqlrr.GetValue(0).ToString();
                            if (targetname.Length==0)
                            {
                                targetname = "NONE";
                            }
                        }

                        sqlrr.Dispose();
                        sqlstr = "exec InsertOthers '" + stdnum + "','" + targetname + "','" + date.ToString("d") + "',0";
                        sqlcomm = new SqlCommand(sqlstr, Sqlconn);
                        sqlcomm.ExecuteNonQuery();  
                    }
                    //如果表中已经记录了该生今年的体测信息，则更新该生体测信息，使用update语句
                    //因为可能该生重新测试了一次体测，所以我这里做成了可以更新今年数据的模式
                    else {
                        //添加身高信息
                        sqlrr.Dispose();
                        sqlstr = "update PhysiqueTest set Result="+height+" where UserId='"+stdnum+"' AND TestName='身高' AND TestDate='"+date.ToString("d")+"'";
                        sqlcomm = new SqlCommand(sqlstr, Sqlconn);
                        sqlcomm.ExecuteNonQuery();

                        //添加体重信息
                        sqlstr = "update PhysiqueTest set Result="+ weight + " where UserId='" + stdnum + "' AND TestName='体重' AND TestDate='" + date.ToString("d") + "'";
                        sqlcomm = new SqlCommand(sqlstr, Sqlconn);
                        sqlcomm.ExecuteNonQuery();

                        //添加肺活量
                        sqlstr = "update PhysiqueTest set Result=" + vitalcapacity + " where UserId='" + stdnum + "' AND TestName='肺活量' AND TestDate='" + date.ToString("d") + "'";
                        sqlcomm = new SqlCommand(sqlstr, Sqlconn);
                        sqlcomm.ExecuteNonQuery();

                        //添加耐力项目
                        sqlrr.Dispose();
                        sqlstr = "select TestName from TaskIDTable where TestId=" + staminanum;
                        sqlcomm = new SqlCommand(sqlstr, Sqlconn);
                        sqlrr = sqlcomm.ExecuteReader();
                        sqlrr.Read();
                        targetname = sqlrr.GetValue(0).ToString();

                        sqlrr.Dispose();
                        sqlstr = "update PhysiqueTest set Result=" + staminacapacity + " where UserId='" + stdnum + "' AND TestName='" + targetname + "' AND TestDate='" + date.ToString("d") + "'";
                        sqlcomm = new SqlCommand(sqlstr, Sqlconn);
                        sqlcomm.ExecuteNonQuery();

                        //添加柔韧力量项目
                        sqlrr.Dispose();
                        sqlstr = "select TestName from TaskIDTable where TestId=" + strengthnum;
                        sqlcomm = new SqlCommand(sqlstr, Sqlconn);
                        sqlrr = sqlcomm.ExecuteReader();
                        sqlrr.Read();
                        targetname = sqlrr.GetValue(0).ToString();

                        sqlrr.Dispose();
                        sqlstr = "update PhysiqueTest set Result=" + strengthcapacity + " where UserId='" + stdnum + "' AND TestName='" + targetname + "' AND TestDate='" + date.ToString("d") + "'";
                        sqlcomm = new SqlCommand(sqlstr, Sqlconn);
                        sqlcomm.ExecuteNonQuery();


                        //添加速度灵巧类项目
                        sqlrr.Dispose();
                        sqlstr = "select TestName from TaskIDTable where TestId=" + speednum;
                        sqlcomm = new SqlCommand(sqlstr, Sqlconn);
                        sqlrr = sqlcomm.ExecuteReader();
                        sqlrr.Read();
                        targetname = sqlrr.GetValue(0).ToString();

                        sqlrr.Dispose();
                        sqlstr = "update PhysiqueTest set Result=" + speedcapacity + " where UserId='" + stdnum + "' AND TestName='" + targetname + "' AND TestDate='" + date.ToString("d") + "'";
                        sqlcomm = new SqlCommand(sqlstr, Sqlconn);
                        sqlcomm.ExecuteNonQuery();

                        //添加奖惩信息
                        if (othernum.Length == 0)
                        {
                            targetname = "NONE";

                        }
                        else
                        {
                            sqlrr.Dispose();
                            sqlstr = "select TestName from TaskIDTable where TestId=" + othernum;
                            sqlcomm = new SqlCommand(sqlstr, Sqlconn);
                            sqlrr = sqlcomm.ExecuteReader();
                            sqlrr.Read();
                            targetname = sqlrr.GetValue(0).ToString();
                            if (targetname.Length==0)
                            {
                                targetname = "NONE";
                            }
                        }

                        sqlrr.Dispose();
                        sqlstr = "exec InsertOthers '" +stdnum + "','" + targetname +"','" + date.ToString() + "',0";
                        SQLRE = sqlstr;
                        sqlcomm = new SqlCommand(sqlstr, Sqlconn);
                        sqlcomm.ExecuteNonQuery();
                    }

                    //将体测成绩添加到MarkAndLevel表中去
                    //这里我调用存储过程来添加成绩
                    sqlcomm.CommandText = "InsertLevelAndMark";
                    sqlcomm.CommandType = CommandType.StoredProcedure;
                    SqlParameter[] ArrayParams = {
                        new SqlParameter("@UserId",stdnum),
                        new SqlParameter("@HeightWeightMark",int.Parse(HeightWeightMark)),
                        new SqlParameter("@HeightWeightLevel",HeightWeightLevel),
                        new SqlParameter("@VitalWeightMark",int.Parse(VitalMark)),
                        new SqlParameter("@VitalWeightLevel",VitalLevel),
                        new SqlParameter("@StaminaMark",int.Parse(StaminaMark)),
                        new SqlParameter("@StaminaLevel",StaminaLevel),
                        new SqlParameter("@StrengthMark",int.Parse(StrengthMark)),
                        new SqlParameter("@StrengthLevel",StrengthLevel),
                        new SqlParameter("@SpeedMark",int.Parse(SpeedMark)),
                        new SqlParameter("@SpeedLevel",SpeedLevel),
                        new SqlParameter("@AllMark",int.Parse(AllMark)),
                        new SqlParameter("@AllLevel",AllLevel),
                        new SqlParameter("@TestDate",date)
                    };
                    foreach (SqlParameter param in ArrayParams)
                    {
                        sqlcomm.Parameters.Add(param);
                    }
                    sqlcomm.ExecuteNonQuery();                    
                }
            
            btnFileUpload.Text = "共" + j + "条数据库加载成功";
            Sqlconn.Close();
        }
        catch (Exception ex)
        {
            //跳转到错误处理页面。
            Session["ErrorMessage"] = ex.Message+"\nSQL:"+SQLRE;
            Session["AtPage"] = "m_data_up.aspx";
            Response.Redirect("m_error.aspx", false);
            HttpContext.Current.ApplicationInstance.CompleteRequest();
            return; 
        }
        finally
        {
            conn.Close();
        } 
#endregion
        
    }

    
}
